CREATE TABLE Statement

Course- SQL >

This SQL tutorial explains how to use the SQL CREATE TABLE statement with syntax, examples, and practice exercises.

Description

The SQL CREATE TABLE statement allows you to create and define a table.

Syntax

The syntax for the SQL CREATE TABLE statement is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name: The name of the table that you wish to create.
column1, column2: The columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Example

Let's look at a SQL CREATE TABLE example.

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

This SQL CREATE TABLE example creates a table called suppliers which has 3 columns.

The first column is called supplier_id which is created as a number datatype (maximum 10 digits in length) and can not contain null values.
The second column is called supplier_name which is a char datatype (50 maximum characters in length) and also can not contain null values.
The third column is called contact_name which is a char datatype but can contain null values.

Now the only problem with this SQL CREATE TABLE statement is that you have not defined a primary key for the table. We could modify this SQL CREATE TABLE statement and define the supplier_id as the primary key as follows:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Learn about primary keys.

Learn about foreign keys.

Practice Exercise #1:

Create a SQL table called customers that stores customer ID, name, and address information.

Solution for Practice Exercise #1:

The SQL CREATE TABLE statement for the customers table is:

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  address char(50),
  city char(50),
  state char(25),
  zip_code char(10)
);

Practice Exercise #2:

Create a SQL table called customers that stores customer ID, name, and address information.

But this time, the customer ID should be the primary key for the table.

Solution for Practice Exercise #2:

The SQL CREATE TABLE statement for the customers table is:

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  address char(50),
  city char(50),
  state char(25),
  zip_code char(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Practice Exercise #3:

Based on the departments table below, create a SQL table called employees that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.

CREATE TABLE departments
( department_id int NOT NULL,
  department_name char(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #3:

The SQL CREATE TABLE statement for the employees table is:

CREATE TABLE employees
( employee_number int NOT NULL,
  employee_name char(50) NOT NULL,
  department_id int,
  salary int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

PRV MENU NEXT